/*
 * Reference: Web Apps code provided by Prof Charlie Garrod
 * can be used for educational purposes
 * */
package messagepasser;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class LoginDAOSQL {
	private List<Connection> connectionPool = new ArrayList<Connection>();

	private String jdbcDriver;
	private String jdbcURL;
	private String tableName;

	public LoginDAOSQL(String jdbcDriver, String jdbcURL, String tableName) {
		this.jdbcDriver = jdbcDriver;
		this.jdbcURL    = jdbcURL;
		this.tableName  = tableName;

		createTable();
	}

	private synchronized Connection getConnection() {
		if (connectionPool.size() > 0) {
			return connectionPool.remove(connectionPool.size()-1);
		}

		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new AssertionError(e);
		}

		try {
			return DriverManager.getConnection(jdbcURL);
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

	private synchronized void releaseConnection(Connection con) {
		connectionPool.add(con);
	}

	private void createTable() {
		Connection con = getConnection();
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(
					"create table "+tableName+
					" (username varchar(20) NOT NULL," +
					" updates varchar(30)," +
					" id MEDIUMINT auto_increment, primary key(id)," +
			" foreign key(username) references aj1_profile(username))");
			stmt.close();
			releaseConnection(con);
		} catch (SQLException e) {
			
			try { con.close(); } catch (SQLException e2) { /* ignore */ }
		//	System.out.println("Exception creating table: "+e);
		//	System.out.println("...assume it already exists");
		}
	}

	public String getPwd(String user) {
		try {
			Connection con = getConnection();
			String pd = null;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select password from userprofile where username = \'"+user+"\' ");
			while(rs.next()){
				pd = rs.getString("password");
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return pd;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	public int getUserID(String user) {
		try {
			Connection con = getConnection();
			int userid = -1;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select userid from userprofile where username = \'"+user+"\' ");
			while(rs.next()){
				userid = rs.getInt("userid");
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return userid;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}




	//set Ip addr and port after successful login
	public void setIpPort(String ip, int port, int userid){
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.executeUpdate("update userstatus set isonline = 1, ip = \'"+ip+"\', port = "+port+" where userid = \'"+userid+"\'"); 

			stmt.close();
			releaseConnection(con);


		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

	//After the user Logs out it removes the IP address from the table
	public void removeIpPort(int userid){
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.executeUpdate("update userstatus set isonline = 0, ip = \'\', port = 0 where userid = \'"+userid+"\'"); 

			stmt.close();
			releaseConnection(con);


		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
}
